package navtreeimpl

import (
	"context"
	"encoding/json"
	"regexp"
	"sort"
	"strings"

	contextmodel "github.com/grafana/grafana/pkg/services/contexthandler/model"
	"github.com/grafana/grafana/pkg/services/navtree"
	"github.com/grafana/grafana/pkg/services/sqlstore"

	_ "github.com/go-sql-driver/mysql"
)

func (s *ServiceImpl) buildCustomNavLinks(c *contextmodel.ReqContext) []*navtree.NavLink {

	namespace := "nav_tree"
	cacheKey := c.SignedInUser.Login
	orgId := c.SignedInUser.OrgID

	// Try to get data from cache first
	if s.kvStore != nil {
		cachedData, found, err := s.kvStore.Get(c.Req.Context(), orgId, namespace, cacheKey)
		if err == nil && found && cachedData != "" {
			// Deserialize the cached data
			var cachedNavLinks []*navtree.NavLink
			if err := json.Unmarshal([]byte(cachedData), &cachedNavLinks); err == nil && len(cachedNavLinks) > 0 {
				s.log.Info("Using cached navigation tree for user", "login", c.SignedInUser.Login)

				// Start asynchronous refresh of the cache
				go func() {
					// Create a new context since the original one might be canceled
					bgCtx := context.Background()
					updatedNavs := s.fetchNavLinksFromDB(bgCtx, c)
					if len(updatedNavs) > 0 {
						// Serialize the updated navigation links
						jsonData, err := json.Marshal(updatedNavs)
						if err == nil {
							if err := s.kvStore.Set(bgCtx, orgId, namespace, cacheKey, string(jsonData)); err != nil {
								s.log.Error("Failed to update navigation cache", "error", err)
							}
							//fmt.Println(string(jsonData))
						} else {
							s.log.Error("Failed to serialize navigation data", "error", err)
						}
					}
				}()

				// Return cached data
				return cachedNavLinks
			}
		}
	}

	s.log.Info("No cached navigation tree for user", "login", c.SignedInUser.Login)

	// If cache miss or error, fetch from database
	navLinks := s.fetchNavLinksFromDB(c.Req.Context(), c)

	// Update cache with fresh data
	if s.kvStore != nil && len(navLinks) > 0 {
		jsonData, err := json.Marshal(navLinks)
		if err == nil {
			err := s.kvStore.Set(c.Req.Context(), orgId, namespace, cacheKey, string(jsonData))
			if err != nil {
				s.log.Error("Failed to cache navigation tree", "error", err)
			}
		} else {
			s.log.Error("Failed to serialize navigation data", "error", err)
		}
	}

	return navLinks
}

func (s *ServiceImpl) buildFixedNav(c *contextmodel.ReqContext) map[string]navtree.NavLink {
	fixedNav := make(map[string]navtree.NavLink, 0)

	err := s.db.WithDbSession(c.Req.Context(), func(sess *sqlstore.DBSession) error {
		sql := `select uid as id, title as text from dashboard where title in ('业务观测','成本运营','风险管理','基础运维','厂内工具')`

		// 执行一次性查询
		allResults, err := sess.Query(sql)
		if err != nil {
			s.log.Error("", err)
		}

		// 创建所有NavLink对象
		for _, row := range allResults {

			id := string(row["id"])

			text := string(row["text"])

			fixedNav[text] = navtree.NavLink{
				Id:    id,
				Text:  text,
				Icon:  "",
				Url:   "",
				IsNew: false,
			}
		}

		return nil
	})

	if err != nil {
		s.log.Error("Failed to fetch fixed nav", "error", err)
	}

	return fixedNav
}

func (s *ServiceImpl) fetchNavLinksFromDB(ctx context.Context, c *contextmodel.ReqContext) []*navtree.NavLink {
	listedProjectNavs := make([]*navtree.NavLink, 0)
	projectNavs := make([]*navtree.NavLink, 0)

	err := s.db.WithDbSession(ctx, func(sess *sqlstore.DBSession) error {

		allCheck := map[string]bool{}
		dashboardCheck := map[string]bool{}
		foldersCheck := map[string]bool{}

		for _, pinfo := range c.SignedInUser.Permissions {
			for _, cinfo := range pinfo {
				for _, detail := range cinfo {
					if strings.Contains(detail, "dashboards:uid") {
						dashboardCheck[strings.Replace(detail, "dashboards:uid:", "", -1)] = true
						allCheck[strings.Replace(detail, "dashboards:uid:", "", -1)] = true
					}
					if strings.Contains(detail, "folders:uid") {
						foldersCheck[strings.Replace(detail, "folders:uid:", "", -1)] = true
						allCheck[strings.Replace(detail, "folders:uid:", "", -1)] = true
					}
				}
			}
		}
		foldersList := []string{}
		for folder, _ := range foldersCheck {
			foldersList = append(foldersList, folder)
		}

		allList := []string{}
		for all_dash_folder, _ := range allCheck {
			allList = append(allList, all_dash_folder)
		}

		combinedQuery := `
		WITH RECURSIVE cte1 AS (
			SELECT uid,folder_uid
			FROM dashboard
			WHERE uid in ('` + strings.Join(allList, "','") + `')
			UNION ALL
			SELECT y.uid, y.folder_uid
			FROM dashboard y
			JOIN cte1 ON y.uid = cte1.folder_uid
		),cte2 AS (
			SELECT uid,folder_uid
			FROM dashboard
			WHERE uid in ('` + strings.Join(allList, "','") + `')
			UNION ALL
			SELECT y.uid, y.folder_uid
			FROM dashboard y
			JOIN cte2 ON y.folder_uid = cte2.uid
		)
		SELECT distinct folder_uid as uid
		FROM cte1  WHERE folder_uid is not null
		union all
		SELECT distinct uid
		FROM cte2  WHERE uid is not null;
		`

		rows, _ := sess.Query(combinedQuery)
		for _, info := range rows {
			uid := string(info["uid"])
			if _, ok := allCheck[uid]; !ok {
				allList = append(allList, uid)
			}
		}

		sql := `
		WITH RECURSIVE FolderHierarchy AS (
			SELECT 
				uid,
				title,
				folder_uid,
				uid AS top_folder_uid
			FROM 
				dashboard
			WHERE 
				folder_uid IS NULL OR folder_uid = ''
		
			UNION ALL
		
			SELECT 
				d.uid,
				fh.title,
				d.folder_uid,
				fh.top_folder_uid
			FROM 
				dashboard d
			INNER JOIN 
				FolderHierarchy fh ON d.folder_uid = fh.uid
		)
		select
			d.uid as id,
			d.title AS text,
			fh.title as terms,
			if(d.folder_uid in("fdgg8zymuuio0f","ddghtwh01cmwwa","ddghtxf63uku8d","cdghty4d4z8xsf","cdgolthgyzgg0a","fdqfycck2oe80a"),'0',d.folder_uid) as fid,
			slug,
			JSON_UNQUOTE(JSON_EXTRACT(data, '$.icon')) as icon,
			JSON_UNQUOTE(JSON_EXTRACT(data, '$.description')) as url,
			is_folder
		FROM 
			dashboard d
		LEFT JOIN 
			(SELECT uid, top_folder_uid,title FROM FolderHierarchy) fh 
		ON 
			d.uid = fh.uid 
			where fh.title in ('业务观测','成本运营','风险管理','基础运维','厂内工具') and folder_uid is not null  AND d.title not like "\_%"
			and ( d.folder_uid in ('` + strings.Join(foldersList, "','") + `') or d.uid in ('` + strings.Join(allList, "','") + `') )
		`

		// 执行一次性查询
		allResults, err := sess.Query(sql)
		if err != nil {
			s.log.Error("", err)
		}

		//projectNavs := make([]*navtree.NavLink, 0)
		allNavLinks := make(map[string]*navtree.NavLink)

		// 创建所有NavLink对象
		for _, row := range allResults {

			id := string(row["id"])

			text := string(row["text"])
			terms := string(row["terms"])

			slug := string(row["slug"])
			url := string(row["url"])

			default_target := ""
			default_url := s.cfg.AppSubURL + "/d/" + id + "/" + slug + "?orgId=1"
			if strings.Contains(text, "[link]") {
				default_url = url
				default_target = "_blank"
			} else if strings.Contains(text, "[inlink]") {
				default_url = s.cfg.AppSubURL + url
			}

			default_icon := "home-alt"
			if string(row["is_folder"]) == "1" {
				default_icon = "apps"
				default_url = "#"
			}
			if string(row["icon"]) != "" {
				default_icon = string(row["icon"])
			}

			navLink := &navtree.NavLink{
				Id:       id,
				Text:     text,
				PluginID: terms,
				Url:      default_url,
				Icon:     default_icon,
				Target:   default_target,
				Children: make([]*navtree.NavLink, 0),
			}

			allNavLinks[id] = navLink
		}

		// 创建子到父的映射关系，用于判断节点是否为根节点
		childToParent := make(map[string]string)

		// 记录所有子项的父项ID
		for _, row := range allResults {
			id := string(row["id"])
			folderID := string(row["fid"])
			childToParent[id] = folderID
		}

		//找出所有根节点（没有父节点的节点）
		for id := range allNavLinks {
			if childToParent[id] == "0" {
				projectNavs = append(projectNavs, allNavLinks[id])
			}
		}

		//递归构建子节点
		buildChildren(allNavLinks, childToParent)

		/*
			go func() {
				err = s.updateUserFolderAuth(sess, c.SignedInUser.Login, allList)
				if err != nil {
					s.log.Error("更新用户授权目录失败", err)
				}
			}()
		*/

		go func() {
			// 创建一个新的背景上下文
			bgCtx := context.Background()

			// 使用新的上下文创建新的数据库会话
			err := s.db.WithDbSession(bgCtx, func(newSess *sqlstore.DBSession) error {
				return s.updateUserFolderAuth(newSess, c.SignedInUser.Login, allList)
			})

			if err != nil {
				s.log.Error("更新用户授权目录失败", err)
			}
		}()

		return nil
	})
	if err != nil {
		s.log.Error("Get Custom Nav Error", "err", err)
	}

	listedProjectNavs = sortNavLinks(projectNavs)
	cleanNavLinkTexts(listedProjectNavs)

	return listedProjectNavs

}

func sortNavLinks(navLinks []*navtree.NavLink) []*navtree.NavLink {
	sortedNavLinks := make([]*navtree.NavLink, 0, len(navLinks))
	for _, navLink := range navLinks {
		// 如果当前导航链接有子链接，则递归排序子链接
		if len(navLink.Children) > 0 {
			navLink.Children = sortNavLinks(navLink.Children)
		}
		sortedNavLinks = append(sortedNavLinks, navLink)
	}

	// 对当前层的导航链接进行排序
	sort.Slice(sortedNavLinks, func(i, j int) bool {
		return sortedNavLinks[i].Text < sortedNavLinks[j].Text
	})

	return sortedNavLinks
}

// 定义一个递归函数，用于替换导航链接及其子链接中的特定模式
func cleanNavLinkTexts(navLinks []*navtree.NavLink) {
	pattern := `\[\d+\]`
	re := regexp.MustCompile(pattern)

	for _, navLink := range navLinks {
		// 替换当前导航链接的文本中的特定模式
		navLink.Text = re.ReplaceAllString(navLink.Text, "")
		navLink.Text = strings.Replace(navLink.Text, "[link]", "", -1)
		navLink.Text = strings.Replace(navLink.Text, "[inlink]", "", -1)

		// 如果当前导航链接有子链接，则递归处理子链接
		if len(navLink.Children) > 0 {
			cleanNavLinkTexts(navLink.Children)
		}
	}
}

// buildChildren 递归构建NavLink的子节点
func buildChildren(allNavLinks map[string]*navtree.NavLink, childToParent map[string]string) {
	// 创建父到子的映射关系
	parentToChildren := make(map[string][]string)

	for childID, parentID := range childToParent {
		if _, exists := parentToChildren[parentID]; !exists {
			parentToChildren[parentID] = make([]string, 0)
		}
		parentToChildren[parentID] = append(parentToChildren[parentID], childID)
	}

	// 使用递归函数填充子节点
	var fillChildren func(string)
	fillChildren = func(nodeID string) {
		// 获取当前节点的所有子节点ID
		childIDs, hasChildren := parentToChildren[nodeID]
		if !hasChildren {
			return // 没有子节点，结束递归
		}

		// 获取当前节点
		node := allNavLinks[nodeID]

		// 清空子节点列表，避免重复添加
		node.Children = make([]*navtree.NavLink, 0, len(childIDs))

		// 添加所有子节点
		for _, childID := range childIDs {
			childNode := allNavLinks[childID]
			node.Children = append(node.Children, childNode)

			// 递归处理子节点的子节点
			fillChildren(childID)
		}
	}

	// 对所有节点应用递归函数
	for nodeID := range allNavLinks {
		fillChildren(nodeID)
	}
}

func (s *ServiceImpl) updateUserFolderAuth(sess *sqlstore.DBSession, login string, foldersList []string) error {

	if len(foldersList) > 0 {
		// 开始事务
		err := sess.Begin()
		if err != nil {
			s.log.Error("Failed to begin transaction", "err", err)
			return err
		}

		// 确保事务在错误时回滚
		defer func() {
			if p := recover(); p != nil {
				sess.Rollback()
				//panic(p) // 重新触发 panic
			} else if err != nil {
				sess.Rollback()
			} else {
				err = sess.Commit()
				if err != nil {
					s.log.Error("Failed to commit transaction", "err", err)
				}
			}
		}()

		var valueStrings []string
		var valueArgs []interface{}
		for _, folder := range foldersList {
			valueStrings = append(valueStrings, "(?, ?, 1)")
			valueArgs = append(valueArgs, login, folder)
		}
		insertSQL := `INSERT INTO user_folder_auth (login, folder_uid, update_flag) VALUES ` + strings.Join(valueStrings, ",") + ` ON DUPLICATE KEY UPDATE update_flag = VALUES(update_flag)`

		args := append([]interface{}{insertSQL}, valueArgs...)
		_, err = sess.Exec(args...)
		if err != nil {
			s.log.Error("Insert User Folder Auth Error", "err", err)
			return err
		}

		deleteSQL := `DELETE FROM user_folder_auth WHERE login = ? AND update_flag = 0`
		_, err = sess.Exec(deleteSQL, login)
		if err != nil {
			s.log.Error("Delete Old User Folder Auth Error", "err", err)
			return err
		}

		updateSQL := `UPDATE user_folder_auth SET update_flag = 0 WHERE login = ?`
		_, err = sess.Exec(updateSQL, login)
		if err != nil {
			s.log.Error("Update User Folder Auth Error", "err", err)
			return err
		}
	}
	return nil
}
